using Infrastructure;
using Infrastructure.Attribute;
using ZR.Model;
using ZR.Model.System;
using ZR.Model.System.Dto;
using ZR.Repository;
using ZR.ServiceCore.Services.IService;

namespace ZR.ServiceCore.Services
{
    /// <summary>
    /// 风力数据DBS查询服务实现
    /// </summary>
    [AppService(ServiceType = typeof(IWindDataDBSService), ServiceLifetime = LifeTime.Transient)]
    public class WindDataDBSService : BaseService<WindDataDBS>, IWindDataDBSService
    {
        /// <summary>
        /// 获取所有DBS表名列表（包含Z_RADR_I的表，包括DBS和PPI表）
        /// </summary>
        /// <returns></returns>
        public List<string> GetAllDBSTableNames()
        {
            try
            {
                // 查询所有包含Z_RADR_I的表（包括DBS和PPI表）
                var allTablesSql = @"
                    SELECT TABLE_NAME 
                    FROM INFORMATION_SCHEMA.TABLES 
                    WHERE TABLE_TYPE = 'BASE TABLE' 
                    AND TABLE_NAME LIKE '%P_CDWL%'";
                
                var allTables = Context.Ado.SqlQuery<string>(allTablesSql).ToList();                
                // 分别统计DBS表和PPI表
                var dbsTables = allTables.Where(table => table.Contains("LR_096")).ToList();
                var ppiTables = allTables.Where(table => !table.Contains("LR_096")).ToList();
               
                
                // 返回所有表（DBS服务现在可以处理两种类型的表）
                return allTables;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取DBS表名列表时出错: {ex.Message}");
                Console.WriteLine($"错误堆栈: {ex.StackTrace}");
                return new List<string>();
            }
        }

        /// <summary>
        /// 根据时间参数获取对应的DBS表名列表
        /// </summary>
        /// <param name="timeParam">时间参数</param>
        /// <returns></returns>
        public List<string> GetDBSTableNames(string timeParam)
        {
            try
            {
                var allTables = GetAllDBSTableNames();
                
                if (string.IsNullOrEmpty(timeParam))
                {
                    return allTables;
                }

                // 根据时间参数过滤表名
                return allTables.Where(table => table.Contains(timeParam)).ToList();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"根据时间参数获取DBS表名时出错: {ex.Message}");
                return new List<string>();
            }
        }

        /// <summary>
        /// 获取风力数据DBS查询列表
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <param name="pager">分页参数</param>
        /// <returns></returns>
        public PagedInfo<WindDataDBS> SelectWindDataDBSList(WindDataDBSQueryDto query, PagerInfo pager)
        {
            var allData = new List<WindDataDBS>();
            
            // 根据TimeParam获取包含该时间参数的表名列表
            var sourceTables = GetDBSTableNames(query.TimeParam);
            
            Console.WriteLine($"DBS查询 - TimeParam: {query.TimeParam}, 找到表数量: {sourceTables.Count}");
            Console.WriteLine($"DBS查询 - 表名列表: {string.Join(", ", sourceTables)}");

            foreach (var tableName in sourceTables)
            {
                try
                {
                    var tableData = GetDataFromTable(tableName, query);
                    Console.WriteLine($"DBS查询 - 表 {tableName} 返回数据量: {tableData.Count}");
                    allData.AddRange(tableData);
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"DBS查询 - 查询表 {tableName} 时出错: {ex.Message}");
                }
            }

            Console.WriteLine($"DBS查询 - 总数据量: {allData.Count}");

            // 应用过滤条件
            var filteredData = ApplyFilters(allData, query);
            Console.WriteLine($"DBS查询 - 过滤后数据量: {filteredData.Count}");

            // 排序
            filteredData = filteredData.OrderBy(x => x.SourceTable).ThenBy(x => x.Id).ToList();

            // 分页
            var totalCount = filteredData.Count;
            var pagedData = filteredData.Skip((pager.PageNum - 1) * pager.PageSize).Take(pager.PageSize).ToList();

            return new PagedInfo<WindDataDBS>
            {
                Result = pagedData,
                TotalNum = totalCount,
                PageIndex = pager.PageNum,
                PageSize = pager.PageSize
            };
        }

        /// <summary>
        /// 从指定表获取数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        private List<WindDataDBS> GetDataFromTable(string tableName, WindDataDBSQueryDto query)
        {
            try
            {
                // 构建查询SQL
                var sql = BuildSelectSql(tableName, query);
                Console.WriteLine($"DBS查询 - 执行SQL: {sql}");
                
                // 根据表名判断字段类型
                bool isDBS = tableName.Contains("LR_096");
                
                // 构建参数对象
                var parameters = new
                {
                    id = query.Id,
                    height = query.Height,
                    verticalSpeed = query.VerticalSpeed,
                    windSpeed = query.WindSpeed,
                    windDirection = query.WindDirection
                };
                
                // 使用构建的SQL和参数直接查询
                var result = Context.Ado.SqlQuery<WindDbsData>(sql, parameters);
                
                Console.WriteLine($"DBS查询 - SQL查询返回行数: {result.Count}");
                
                // 将WindDbsData转换为WindDataDBS
                var dbsList = result.Select(windData => new WindDataDBS
                {
                    Id = windData.Id,
                    SourceTable = tableName,
                    WindDirection = windData.WindDirection,
                    WindSpeed = windData.WindSpeed,
                    Height = windData.Height,  // DBS表用height，PPI表用x_coordinate
                    VerticalSpeed =windData.VerticalSpeed,  // DBS表用vertical_speed，PPI表用y_coordinate
                }).ToList();
                return dbsList;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"查询表 {tableName} 时出错: {ex.Message}");
                Console.WriteLine($"错误堆栈: {ex.StackTrace}");
                return new List<WindDataDBS>();
            }
        }

        /// <summary>
        /// 构建查询SQL
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        private string BuildSelectSql(string tableName, WindDataDBSQueryDto query)
        {
            // 根据表名判断字段类型
            bool isDBS = tableName.Contains("LR_096");
            string selectFields;
            
            if (isDBS)
            {
                // DBS表字段：height, vertical_speed, wind_speed, wind_direction, time
                selectFields = "id, height, wind_direction, wind_speed, vertical_speed";
            }
            else
            {
                // PPI表字段：x_coordinate, y_coordinate, wind_speed, wind_direction, time
                selectFields = "id, x_coordinate, y_coordinate, wind_speed, wind_direction";
            }
            
            var whereConditions = new List<string>();

            // 添加表名过滤
            if (!string.IsNullOrEmpty(query.SourceTable) && query.SourceTable != tableName)
            {
                return "SELECT 1 WHERE 1=0"; // 返回空结果
            }

            // 添加其他过滤条件（使用参数化查询避免SQL注入）
            if (query.Id.HasValue)
            {
                whereConditions.Add($"id = @id");
            }

            if (isDBS)
            {
                // DBS表过滤条件
                if (!string.IsNullOrEmpty(query.Height))
                {
                    whereConditions.Add($"height = @height");
                }
                if (!string.IsNullOrEmpty(query.VerticalSpeed))
                {
                    whereConditions.Add($"vertical_speed = @verticalSpeed");
                }
            }
            else
            {
                // PPI表过滤条件 - 将Height和VerticalSpeed映射到坐标字段
                if (!string.IsNullOrEmpty(query.Height))
                {
                    whereConditions.Add($"x_coordinate = @height");
                }
                if (!string.IsNullOrEmpty(query.VerticalSpeed))
                {
                    whereConditions.Add($"y_coordinate = @verticalSpeed");
                }
            }

            // 通用过滤条件
            if (!string.IsNullOrEmpty(query.WindDirection))
            {
                whereConditions.Add($"wind_direction = @windDirection");
            }

            if (!string.IsNullOrEmpty(query.WindSpeed))
            {
                whereConditions.Add($"wind_speed = @windSpeed");
            }

            // 添加数据有效性筛选条件
            if (isDBS)
            {
                // DBS表筛选条件
                whereConditions.Add("height != '-9999.00'");
                whereConditions.Add("vertical_speed != '-9999.00'");
                whereConditions.Add("wind_speed != '-9999.00'");
                whereConditions.Add("wind_direction != '-9999.00'");
            }
            else
            {
                // PPI表筛选条件
                whereConditions.Add("x_coordinate != '-9999.00'");
                whereConditions.Add("y_coordinate != '-9999.00'");
                whereConditions.Add("wind_speed != '-9999.00'");
                whereConditions.Add("wind_direction != '-9999.00'");
            }

            var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";
            return $"SELECT {selectFields} FROM {tableName} {whereClause} LIMIT 1000";
        }

        /// <summary>
        /// 转换为WindDataDBS对象
        /// </summary>
        /// <param name="row">数据行</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        private WindDataDBS ConvertToWindDataDBS(Dictionary<string, object> row, string tableName)
        {
            // 根据表名判断字段类型
            bool isDBS = tableName.Contains("LR_096");
            
            var result = new WindDataDBS
            {
                Id = GetValue<int>(row, "id"),
                SourceTable = tableName,
                WindDirection = GetValue<string>(row, "wind_direction"),
                WindSpeed = GetValue<string>(row, "wind_speed")
            };

            if (isDBS)
            {
                // DBS表字段
                result.Height = GetValue<string>(row, "height");
                result.VerticalSpeed = GetValue<string>(row, "vertical_speed");
            }
            else
            {
                // PPI表字段 - 将坐标字段映射到DBS字段
                result.Height = GetValue<string>(row, "x_coordinate");
                result.VerticalSpeed = GetValue<string>(row, "y_coordinate");
            }

            return result;
        }

        /// <summary>
        /// 从字典中获取值
        /// </summary>
        /// <typeparam name="T">值类型</typeparam>
        /// <param name="dict">字典</param>
        /// <param name="key">键</param>
        /// <returns></returns>
        private T GetValue<T>(Dictionary<string, object> dict, string key)
        {
            if (dict == null || !dict.ContainsKey(key) || dict[key] == null)
            {
                return default(T);
            }

            try
            {
                var value = dict[key];
                if (value is T)
                {
                    return (T)value;
                }

                // 特殊处理可空类型
                if (typeof(T).IsGenericType && typeof(T).GetGenericTypeDefinition() == typeof(Nullable<>))
                {
                    var underlyingType = Nullable.GetUnderlyingType(typeof(T));
                    if (underlyingType != null)
                    {
                        return (T)Convert.ChangeType(value, underlyingType);
                    }
                }

                return (T)Convert.ChangeType(value, typeof(T));
            }
            catch
            {
                return default(T);
            }
        }

        /// <summary>
        /// 应用过滤条件
        /// </summary>
        /// <param name="data">数据列表</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        private List<WindDataDBS> ApplyFilters(List<WindDataDBS> data, WindDataDBSQueryDto query)
        {
            var filteredData = data.AsQueryable();

            if (!string.IsNullOrEmpty(query.SourceTable))
            {
                filteredData = filteredData.Where(x => x.SourceTable == query.SourceTable);
            }

            if (!string.IsNullOrEmpty(query.Height))
            {
                filteredData = filteredData.Where(x => x.Height == query.Height);
            }

            if (!string.IsNullOrEmpty(query.WindDirection))
            {
                filteredData = filteredData.Where(x => x.WindDirection == query.WindDirection);
            }

            if (!string.IsNullOrEmpty(query.WindSpeed))
            {
                filteredData = filteredData.Where(x => x.WindSpeed == query.WindSpeed);
            }

            if (!string.IsNullOrEmpty(query.VerticalSpeed))
            {
                filteredData = filteredData.Where(x => x.VerticalSpeed == query.VerticalSpeed);
            }

            return filteredData.ToList();
        }

        /// <summary>
        /// 模糊查询风力数据DBS查询列表
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <param name="pager">分页参数</param>
        /// <returns></returns>
        public PagedInfo<WindDataDBS> SelectWindDataDBSListWithLike(WindDataDBSQueryDto query, PagerInfo pager)
        {
            return SelectWindDataDBSList(query, pager);
        }

        /// <summary>
        /// 获取风力数据DBS查询统计信息
        /// </summary>
        /// <returns></returns>
        public object GetWindDataDBSStatistics()
        {
            var sourceTables = GetAllDBSTableNames();
            var statistics = new Dictionary<string, object>();

            foreach (var tableName in sourceTables)
            {
                try
                {
                    var countSql = $"SELECT COUNT(*) as total FROM {tableName}";
                    var result = Context.Ado.SqlQuery<Dictionary<string, object>>(countSql);
                    var count = GetValue<long>(result.FirstOrDefault(), "total");
                    statistics[tableName] = count;
                }
                catch (Exception ex)
                {
                    statistics[tableName] = $"错误: {ex.Message}";
                }
            }

            return new
            {
                SourceTables = sourceTables,
                TableStatistics = statistics,
                TotalTables = sourceTables.Count
            };
        }
    }
}